package org.example.dao;

import org.example.entity.Sc;
import org.example.util.DButil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;

public class ScDao {
    public static ArrayList<Sc> findAllSc(){

        // 泛型
        ArrayList<Sc> arrayList = new ArrayList<>();

        // 查询数据
        Connection connection = DButil.getConnection();
        PreparedStatement statement = null;
        ResultSet resultSet = null;

        String sql = "select * from sc ";

        try {
            // 获取预处理对象
            statement = connection.prepareStatement(sql);

            // 执行SQL语句
            resultSet = statement.executeQuery();

            while (resultSet.next()){

                String Scid = resultSet.getString("Id");
                String Sname = resultSet.getString("Sname");
                String Sno = resultSet.getString("Sno");
                String Gender = resultSet.getString("Gender");
                String Address = resultSet.getString("Address");
                String Grade = resultSet.getString("Grade");
                String Score = resultSet.getString("Score");

                // 数据持久化 ==> 实体类
                Sc sc = new Sc();
                sc.setId(Scid);
                sc.setSname(Sname);
                sc.setSno(Sno);
                sc.setGender(Gender);
                sc.setSno(Address);
                sc.setGrade(Grade);
                sc.setAddress(Score);

                arrayList.add(sc);
            }

        } catch (java.sql.SQLException e) {
            throw new RuntimeException(e);
        }
        finally {
            DButil.close(resultSet, statement, connection);
        }

        return arrayList;
    }


    public static boolean  login(String Scid, String Password){
        Connection connection = DButil.getConnection();
        PreparedStatement statement = null;
        ResultSet resultSet = null;

        String sql = "select * from sc where Id = ? and Sname =? ";

        boolean flag = false;

        try {
            // 获取预处理对象
            statement = connection.prepareStatement(sql);

            // 对SQL进行传参
            statement.setString(1, Scid);
            statement.setString(2, Password);

            // 执行SQL语句
            resultSet = statement.executeQuery();
//            statement.executeUpdate()
            if (resultSet.next()){
                flag = true;
            }

        } catch (java.sql.SQLException e) {
            throw new RuntimeException(e);
        }
        finally {
            DButil.close(resultSet, statement, connection);
        }

        return flag;
    }

    public static int addSc(Sc sc) {

        // 泛型

        // 查询数据
        Connection connection = DButil.getConnection();
        PreparedStatement statement = null;
        ResultSet resultSet = null;

        String sql = "insert into sc (Id,Sname,Sno,Gender,Address,Grade,Score) values (?,?,?,?,?,?,?)";
        int row = 0;
        try {
            // 获取预处理对象
            statement = connection.prepareStatement(sql);
            statement.setString(1, sc.getId());
            statement.setString(2, sc.getSname());
            statement.setString(3, sc.getSno());
            statement.setString(4, sc.getGender());
            statement.setString(5, sc.getAddress());
            statement.setString(6, sc.getGrade());
            statement.setString(7, sc.getScore());


            // 执行SQL语句
            // select ----> statement.executeQuery();

            // insert delete update
            row = statement.executeUpdate();

        } catch (java.sql.SQLException e) {
            throw new RuntimeException(e);
        } finally {
            DButil.close(resultSet, statement, connection);
        }

        return row;
    }

    public static int deleteSc(String Scid) {
        Connection connection = DButil.getConnection();
        PreparedStatement statement = null;
        ResultSet resultSet = null;

        String sql = "delete from sc where Id=?";
        int row = 0;

        try {
            // 获取预处理对象
            statement = connection.prepareStatement(sql);
            statement.setString(1, Scid);

            // 执行SQL语句
            // select ----> statement.executeQuery();

            // insert delete update
            row = statement.executeUpdate();

        } catch (java.sql.SQLException e) {
            throw new RuntimeException(e);
        } finally {
            DButil.close(resultSet, statement, connection);
        }

        return row;
    }

    public static int updateSc(Sc sc) {
        Connection connection = DButil.getConnection();
        PreparedStatement statement = null;
        ResultSet resultSet = null;

        String sql = "update sc set Sname=?,Sno = ?,Gender =  ?, Address = ?, Grade = ?, Score = ? where Id = ?";
        int row = 0;
        try {
            // 获取预处理对象
            statement = connection.prepareStatement(sql);
            statement.setString(1, sc.getSname());
            statement.setString(2, sc.getSno());
            statement.setString(3, sc.getGender());
            statement.setString(4, sc.getAddress());
            statement.setString(5, sc.getGrade());
            statement.setString(6, sc.getScore());
            statement.setString(7, sc.getId());


            // 执行SQL语句
            // select ----> statement.executeQuery();

            // insert delete update
            row = statement.executeUpdate();

        } catch (java.sql.SQLException e) {
            throw new RuntimeException(e);
        } finally {
            DButil.close(resultSet, statement, connection);
        }

        return row;
    }

}

